#!D:\Program_Files\Python27\python.exe

import ibm_db
import StringIO

class ConnStr(object):
    def __init__(self, connName="", host="", port="", protocol="", \
                 dbname="", username="", password=""):
        super(object)
        self.__connName = connName
        self.__host = host
        self.__port = port
        self.__protocol = protocol
        self.__dbname = dbname
        self.__username = username
        self.__password = password
        pass

    @property
    def connName(self):return self.__connName
    @connName.setter
    def connName(self, val): self.__connName = val
    
    @property
    def host(self):return self.__host
    @host.setter
    def host(self, val): self.__host = val

    @property
    def port(self):return self.__port
    @port.setter
    def port(self, val): self.__port = val

    @property
    def protocol(self):return self.__protocol
    @protocol.setter
    def protocol(self, val): self.__protocol = val

    @property
    def dbname(self):return self.__dbname
    @dbname.setter
    def dbname(self, val): self.__dbname = val

    @property
    def username(self):return self.__username
    @username.setter
    def username(self, val): self.__username = val

    @property
    def password(self):return self.__password
    @password.setter
    def password(self, val): self.__password = val

    def generateStr(self):
        conn_str = "DATABASE=" + self.dbname + ";HOSTNAME="+ self.host + \
                   ";PORT=" + self.port + ";PROTOCOL=" + self.protocol + \
                   ";UID=" + self.username + ";PWD=" + self.password + ";"
        return conn_str
        pass
    
    def baseTypeObj(self) :
        '''return a base type object which will be used for dump json'''
        rsd = dict() 
        rsd['host'] = self.__host 
        rsd['port'] = self.__port 
        rsd['protocol'] = self.__protocol 
        rsd['dbname'] = self.__dbname 
        rsd['username'] = self.__username 
        rsd['password'] = self.__password 
        return rsd

    @staticmethod
    def getInstanceFromBaseTypeObj(baseTypeObj):
        cs = ConnStr()
        cs.host = baseTypeObj['host']
        cs.port = baseTypeObj['port']
        cs.protocol = baseTypeObj['protocol']
        cs.dbname = baseTypeObj['dbname']
        cs.username = baseTypeObj['username']
        cs.password = baseTypeObj['password']
        return cs
        
    pass

#execScriptsDb2
def execScriptsDb2(catalog, scripts, seperator=";", autocommit=True):
    conn_info = ConnStr.getInstanceFromBaseTypeObj(catalog)

    if autocommit:
        res = execScriptsDb2AutoCommitOn(conn_info, scripts, seperator)
    else:
        res = execScriptsDb2AutoCommitOff(conn_info, scripts, seperator)
        
    return res

#execScriptsDb2AutoCommitOn
def execScriptsDb2AutoCommitOn(conn_info, scripts, seperator=";"):
    res = list()
    conn_opts = dict()
    conn_opts["SQL_ATTR_AUTOCOMMIT"] = "SQL_AUTOCOMMIT_ON"
    conn = ibm_db.connect(conn_info.generateStr(), \
                          conn_info.username, \
                          conn_info.password, \
                          conn_opts)
    if conn==None :
        exit() #exception
        pass
    
    scriptList = splitScripts(scripts, seperator)
    for scr in scriptList:
        aRes = execScriptDb2AutoCommitOn(conn, scr)
        res.append(aRes)
        pass
    
    ibm_db.close(conn)
    del conn
    return res

def splitScripts(scripts, seperator=";"):
    res = list()
    seperator = seperator.decode('utf-8')
    inputString = StringIO.StringIO(scripts)
    tmpString = StringIO.StringIO()
    for aline in inputString.readlines():
        aline = aline.strip()
        tmpString.write(" ")
        tmpString.write(aline)
        if aline.endswith(seperator):
            aStr = tmpString.getvalue()
            res.append(aStr[:-1])
            tmpString.close()
            tmpString = StringIO.StringIO()
            pass
        pass
    if len(tmpString.getvalue())>0:
        aStr = tmpString.getvalue().strip()
        res.append(aStr)
        pass
    inputString.close()
    return res

def execScriptDb2AutoCommitOn(conn, script):
    sql_str = script
    stmt = ibm_db.prepare(conn, sql_str)
    
    ibm_db.execute(stmt)
    rs = ibm_db.num_rows(stmt)
    if rs < 0:
        fieldnum = ibm_db.num_fields(stmt)
        fieldnamelist = list()
        fieldTypeList = list()
        for i in range(fieldnum):
            fieldnamelist.append(ibm_db.field_name(stmt, i))
            fieldTypeList.append(ibm_db.field_type(stmt, i))
            pass
        
        data = list()
        rs = ibm_db.fetch_tuple(stmt)
        while rs!=False:
            tmp = dict()
            for i in range(fieldnum):
                tmp[i] = rs[i]
            data.append(tmp)
            rs = ibm_db.fetch_tuple(stmt)
            pass
        pass
    else :
        data = [{0:rs}]
        fieldnamelist = ["affected rows"]
        fieldTypeList = ["string"]
        pass
    return (data, fieldnamelist, fieldTypeList)
    pass

#execScriptsDb2AutoCommitOff
def execScriptsDb2AutoCommitOff(conn_info, scripts, seperator=";"):
    conn_opts = dict()
    conn_opts["SQL_ATTR_AUTOCOMMIT"] = "SQL_AUTOCOMMIT_OFF" 
    pass

# if __name__ == "__main__":
#     host = "127.0.0.1"
#     dbname = "rake_db"
#     port = "50000"
#     protocol =  "TCPIP"
#     username = "rake"
#     password = "qqq111="
# 
#     cs = ConnStr(host, port, protocol, dbname, username, password)
#     #conn_str = "DATABASE=name;HOSTNAME=host;PORT=60000;PROTOCOL=TCPIP;UID=username;PWD=password;"
#     conn_str = cs.generateStr()
#     conn_opts = dict()
#     conn_opts["SQL_ATTR_AUTOCOMMIT"] = "SQL_AUTOCOMMIT_ON" 
# 
#     conn = ibm_db.connect(conn_str, username, password, conn_opts)
# 
#     if conn==None :
#         exit()
#         pass
# 
#     print "connect to rake_db success"
# 
#     sql_str = """select LSH, TS, MSG
#       from DEBUG.T_DEBUG_MSG"""
# 
#     stmt = ibm_db.prepare(conn, sql_str)
#     #ibm_db.bind_param(stmt, 1, min)
# 
#     ibm_db.execute(stmt)
# 
#     res = ibm_db.fetch_both(stmt)
# 
#     while res!=False:
#         print res[0],"\t|", res[1],"\t|", res[2]
#         res = ibm_db.fetch_both(stmt)
#         pass
# 
#     ibm_db.close(conn)
#     del conn
#     print "disconnect frome rake_db success"
#     pass


